using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Diagnostics;

using phorm.database.core;
using phorm.model.fields;
using phorm.config;

namespace phorm.database.oracle
{
    static class CRUD
    {
        public static string buildTable(DatabaseRecord obj)
        {
            Type type = Type.GetType(obj.ToString());
            List<string> foreignKeys = new List<string>();

            StringBuilder sql = new StringBuilder("");

            sql.AppendFormat("CREATE TABLE \"{0}\"( ", type.Name.ToLower());

            Type foreignKeyType = typeof(ForeignKey<>);

            foreach (KeyValuePair<string, DatabaseColumn> column in obj.columns)
            {
                if (column.Value is PrimaryKey)
                {
                    PrimaryKey tmp = column.Value as PrimaryKey;

                    string auto_increment = tmp.auto_increment ? "auto_increment" : "";
                    sql.AppendFormat("\"{0}\" {1}({2}) NOT NULL {3}, ", column.Key, tmp.field_type, tmp.length, auto_increment);
                }
                else if (!(column.Value is IIgnorableColumn))
                {
                    if (column.Value is Text)
                    {
                        Text tmp = column.Value as Text;
                        sql.AppendFormat("\"{0}\" {1} NULL, ", column.Key, tmp.field_type);
                    }
                    else
                    {
                        DatabaseColumn tmp = column.Value as DatabaseColumn;
                        Type currentType = tmp.GetType();

                        if (currentType.Name.Equals(foreignKeyType.Name))
                        {
                            DatabaseRecord relation = tmp.GetType().GetField("relative").GetValue(tmp) as DatabaseRecord;
                            PrimaryKey primaryKey = relation.GetType().GetField("primary_key").GetValue(relation) as PrimaryKey;
                            Type relationType = relation.GetType();

                            foreignKeys.Add(string.Format(" FOREIGN KEY (\"{0}\") REFERENCES \"{1}\" (\"{2}\") ON DELETE CASCADE, ", tmp.column, relationType.Name.ToString().ToLower(), primaryKey.column));
                        }

                        sql.AppendFormat("\"{0}\" {1}({2}) NULL, ", column.Key, tmp.field_type, tmp.length);
                    }
                }
            }

            if (obj.primary_key != null)
                sql.AppendFormat("PRIMARY KEY  (\"{0}\") ", obj.primary_key.column);

            if (foreignKeys.Count > 0)
            {
                sql.Append(", ");
                foreignKeys.ForEach(
                    delegate(string value)
                    {
                        sql.Append(value);
                    });
                sql.Remove((sql.Length - 2), 2);
            }

            sql.Append(")");
            return sql.ToString();
        }

        public static string buildEmptyQuery(DatabaseRecord obj)
        {
            return string.Format("TRUNCATE TABLE {0}", obj.GetType().Name.ToLower());
        }

        public static string buildDropQuery(DatabaseRecord obj)
        {
            return string.Format("DROP TABLE {0}", obj.GetType().Name.ToLower());
        }

        public static string createManyToMany(BaseManyToMany column)
        {
            Type column2Type = column.GetType().GetGenericArguments()[0];
            Type column1Type = column.model.GetType();

            DatabaseRecord column2Record = Activator.CreateInstance(column2Type) as DatabaseRecord;

            string column1 = string.Format("{0}_{1}", column1Type.Name.ToLower(), column.model.primary_key.column.ToLower());
            string column2 = string.Format("{0}_{1}", column2Type.Name.ToLower(), column2Record.primary_key.column);

            if (column1 == column2)
            {
                column1 += DatabaseRecord.SELF;
                column2 += DatabaseRecord.RELATIVE;
            }

            string sql = string.Format("CREATE TABLE IF NOT EXISTS \"{0}\" (" +
                                       "\"{1}\" {3}({4}) NOT NULL," +
                                       "\"{2}\" {5}({6}) NOT NULL," +
                                       "PRIMARY KEY  (\"{1}\", \"{2}\")," +
                                       "FOREIGN KEY (\"{1}\") REFERENCES \"{7}\" (\"{8}\") ON DELETE CASCADE" +
                                       ");", column.pivot_table, column1, column2, column.model.primary_key.field_type, column.model.primary_key.length, column2Record.primary_key.field_type, column2Record.primary_key.length, column1Type.Name.ToLower(), column.model.primary_key.column);

            return sql;
        }

        public static string buildSaveQuery(DatabaseRecord obj)
        {
            StringBuilder sql = new StringBuilder("");

            if (obj.IsNew)
            {
                sql.AppendFormat("INSERT INTO \"{0}\" (", obj.GetType().Name.ToLower());

                foreach (KeyValuePair<string, DatabaseColumn> column in obj.columns)
                {
                    if (!(column.Value is IIgnorableColumn) && column.Value.hasChanged)
                    {
                        if (column.Value is PrimaryKey)
                        {
                            PrimaryKey tmp = column.Value as PrimaryKey;
                            if (tmp.auto_increment == false)
                                sql.AppendFormat("\"{0}\", ", column.Key);
                        }
                        else
                        {
                            sql.AppendFormat("\"{0}\", ", column.Key);
                        }
                    }
                }

                sql.Remove((sql.Length - 2), 2);
                sql.Append(") VALUES (");

                foreach (KeyValuePair<string, DatabaseColumn> column in obj.columns)
                {
                    if (!(column.Value is IIgnorableColumn) && column.Value.hasChanged)
                    {
                        if (column.Value is PrimaryKey)
                        {
                            PrimaryKey tmp = column.Value as PrimaryKey;
                            if (tmp.auto_increment == false)
                                sql.AppendFormat("@{0}, ", column.Key);
                        }
                        else
                        {
                            sql.AppendFormat("@{0}, ", column.Key);
                        }
                        column.Value.hasChanged = false;
                    }
                }

                sql.Remove((sql.Length - 2), 2);
                sql.Append(")");
            }
            else
            {
                sql.AppendFormat("UPDATE \"{0}\" SET ", obj.GetType().Name.ToLower());
                foreach (KeyValuePair<string, DatabaseColumn> column in obj.columns)
                {
                    if (column.Value.hasChanged && !(column.Value is IIgnorableColumn) && !(column.Value is IInsertOnlyColumn) && !(column.Value is PrimaryKey))
                        sql.AppendFormat("{0}=@{0}, ", column.Key);
                    column.Value.hasChanged = false;
                }

                sql.Remove((sql.Length - 2), 2);
                sql.AppendFormat(" WHERE {0}='{1}'", obj.primary_key.column, obj.primary_key.value);
            }

            return sql.ToString();
        }
    }
}
